<?php

/**
 * @author Murat Cem YALIN <muratcem@gmail.com>
 * @copyright 2014 muratcem.com - walkerPHP.com
 * @license http://opensource.org/licenses/GPL-3.0 GNU General Public License, version 3
 * @version 1.0.1
 */

namespace core\table;

use \core\Db\Db;

class table implements ITable{

    /**
     * stores field names
     * @var array
     */
    private $fields = array();
    /**
     * stores tablename
     * @var string
     */
    private $table;
    /**
     * stores joins
     * @var array
     */
    private $joins = array();
    /**
     * stores where clause
     * @var array
     */
    private $where  = array();
    /**
     * stores where in clause
     * @var array
     */
    private $where_in = array();
    /**
     * stores order by
     * @var array
     */
    private $orderby = array();
    /**
     * stores limit
     * @var array
     */
    private $limit = array();
    /**
     * stores last generated SQL
     * @var string
     */
    private $lastSQL;
    /**
     * stores last generated SQL Params
     * @var array
     */
    private $lastSQLParams = array();

    /**
     * set the table name on construct
     *
     * @param string $table
     */
    function __construct($table){
        $this->table = $table;
    }

    /**
     * resets all select fields and sets new columns
     * you can set $columns as string or array and also
     * like this: $columns = array(array($tablename1, $fieldname1), array($tablename2, $fieldname2), ...)
     * which should give SELECT tablename1.fieldname1, tablename2.fieldname2
     *
     * @param string|array $columns
     * @return $this
     */
    public function select($columns = array('*')){
        $this->fields = array();
        if (is_array($columns)){
            foreach($columns as $value){
                if (is_array($value)){
                    $value = implode('.', $value);
                }
                $this->fields[] = $value;
            }
        } else {
            $this->fields = $columns;
        }
        return $this;
    }

    /**
     * add field to select
     * you can set $columns as string or array and also
     * like this: $columns = array(array($tablename1, $fieldname1), array($tablename2, $fieldname2), ...)
     * which should give SELECT tablename1.fieldname1, tablename2.fieldname2
     *
     * @param string|array $column
     * @return $this
     */
    public function add_select($column){
        if (is_array($column)){
            foreach($column as $value){
                if (is_array($value)){
                    $value = implode('.', $value);
                }
                $this->fields[] = $value;
            }
        } else {
            $this->fields[] = $column;
        }
        return $this;
    }

    /**
     * join tables
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @param string $type LEFT|RIGHT|INNER|OUTER|LEFT OUTER|RIGHT OUTER
     * @return $this
     */
    public function join($table, $on, $type){
        $this->joins[] = array($table, $on, $type.' JOIN');
        return $this;
    }

    /**
     * left join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function left_join($table, $on){
        $this->join($table, $on, 'LEFT');
        return $this;
    }

    /**
     * right join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function right_join($table, $on){
        $this->join($table, $on, 'RIGHT');
        return $this;
    }

    /**
     * inner join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function inner_join($table, $on){
        $this->join($table, $on, 'INNER');
        return $this;
    }

    /**
     * outer join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function outer_join($table, $on){
        $this->join($table, $on, 'OUTER');
        return $this;
    }

    /**
     * left outer join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function left_outer_join($table, $on){
        $this->join($table, $on, 'LEFT OUTER');
        return $this;
    }

    /**
     * right outer join table
     *
     * @param string $table other table name
     * @param array $on array(main_table_fieldname, operator, other_table_fieldname)
     * @return $this
     */
    public function right_outer_join($table, $on){
        $this->join($table, $on, 'RIGHT OUTER');
        return $this;
    }

    /**
     * adds AND where clause
     *
     * @param string $column
     * @param string $sign
     * @param string $value
     * @return $this
     */
    public function where($column, $sign, $value){
        $this->where[] = array($column, $sign, $value, 'AND');
        return $this;
    }

    /**
     * adds OR where clause
     *
     * @param string $column
     * @param string $sign
     * @param string $value
     * @return $this
     */
    public function or_where($column, $sign, $value){
        $this->where[] = array($column, $sign, $value, 'OR');
        return $this;
    }

    /**
     * adds WHERE IN clause
     *
     * @param string $column
     * @param array $array
     * @return $this
     */
    public function where_in($column, $array){
        $this->where_in[] = array($column, $array, 'AND');
        return $this;
    }

    /**
     * adds OR WHERE IN clause
     *
     * @param string $column
     * @param array $array
     * @return $this
     */
    public function or_where_in($column, $array){
        $this->where_in[] = array($column, $array, 'OR');
        return $this;
    }

    /**
     * dynamic where
     *
     * @param $method
     * @param $params
     * @return $this
     */
    public function where_dynamic($method, $params){
        $finder = substr($method, 6);

        $segments = preg_split("/(_and_|_or_)(?=[a-z])/i", $finder, -1, PREG_SPLIT_DELIM_CAPTURE);

        $connector = 'AND';

        $index = 0;

        foreach($segments as $segment){
            if (!in_array($segment, array('_and_', '_or_'))){
                $this->where[] = array($segment, '=', $params[$index], $connector);
                $index++;
            } else {
                $connector = trim(strtoupper($segment), '_');
            }
        }

        return $this;
    }

    /**
     * adds order by
     *
     * @param string $column
     * @param string $asc ASC|DESC
     * @return $this
     */
    public function order_by($column, $asc = 'ASC'){
        if ($column != ''){
            $this->orderby[] = array($column, $asc);
        }
        return $this;
    }

    /**
     * adds limit to query
     *
     * @param int $start
     * @param int $count
     * @return $this
     */
    public function limit($start, $count){
        $start = intval($start);
        $count = intval($count);
        $this->limit = array($start, $count);
        return $this;
    }

    /**
     * adds limit to query by calculating per page counts
     *
     * @param int $page
     * @param int $perPage
     * @return $this
     */
    public function getPage($page, $perPage = 10){
        $page = intval($page);
        $perPage = intval($perPage);
        if ($page > 0){
            $this->limit(($page - 1) * $perPage, $perPage);
        }
        return $this;
    }

    /**
     * Create SQL String
     */
    private function createSQLString(){
        $SQL = 'SELECT SQL_CALC_FOUND_ROWS ';
        if (count($this->fields)){
            $SQL .= implode(', ', $this->fields).' ';
        } else {
            $SQL .= '* ';
        }
        $SQL .= 'FROM '.$this->table.' ';
        if (count($this->joins)){
            foreach($this->joins as $join){
                $SQL .= $join[2].' '.$join[0].' ON '.$this->table.'.'.$join[1][0].' '.$join[1][1].' '.$join[0].'.'.$join[1][2].' ';
            }
        }
        $SQL .= 'WHERE 1=1 ';
        $values = array();
        if (count($this->where)){
            foreach($this->where as $value){
                if (is_array($value[0])){
                    $value[0] = implode('.', $value[0]);
                }
                $SQL .= $value[3].' '.$value[0].$value[1].'? ';
                $values[] = $value[2];
            }
        }
        if (count($this->where_in)){
            foreach($this->where_in as $value){
                $SQL    .= $value[2].' '.$value[0].' IN (';
                for ($i=0; $i<count($value[1]); $i++){
                    $SQL .= '?, ';
                    $values[] = $value[1][$i];
                }
                $SQL = substr($SQL, 0, -2).') ';
            }
        }
        if (count($this->orderby)){
            $orderby = '';
            foreach($this->orderby as $value){
                $orderby .= implode(' ', $value);
            }
            $SQL .= 'ORDER BY '.$orderby;
        }
        if (count($this->limit)){
            $SQL .= 'LIMIT '.$this->limit[0].', '.$this->limit[1];
        }
        $this->lastSQL = $SQL;
        $this->lastSQLParams = $values;
    }

    /**
     * SELECT Query
     *
     * @return array
     */
    public function get(){
        $this->createSQLString();
        return Db::Query($this->lastSQL, $this->lastSQLParams);
    }

    function __toString(){
        $this->createSQLString();
        return $this->lastSQL;
    }

    /**
     * insert set of data to table
     * array("fieldname" => "value")
     *
     * @param array $array
     * @return bool
     */
    public function insert(Array $array){
        if ($this->table != '' && count($array)){
            $SQL            = 'INSERT INTO '.$this->table.' (';
            $insertFields   = '';
            $values         = '';
            $valueArray     = array();
            foreach($array as $key => $value){
                $insertFields .= $key.', ';
                $values       .= '?, ';
                $valueArray[] = $value;
            }
            $insertFields = substr($insertFields, 0, -2);
            $values       = substr($values, 0, -2);
            $SQL         .= $insertFields.') VALUES ('.$values.')';

            $this->lastSQL = $SQL;
            $this->lastSQLParams = $valueArray;
            return Db::unbufferedQuery($SQL, $valueArray);
        } else {
            return FALSE;
        }
    }

    /**
     * update records in table
     * array("fieldname" => "value")
     *
     * @param array $array
     * @return bool
     */
    public function update(Array $array){
        if ($this->table != '' && count($array)){
            $values = array();
            $SQL    = 'UPDATE '.$this->table.' SET ';
            foreach($array as $key => $value){
                $SQL     .= $key.'=?, ';
                $values[] = $value;
            }
            $SQL = substr($SQL, 0, -2);

            if (count($this->where)){
                foreach($this->where as $value){
                    if (is_array($value[0])){
                        $value[0] = implode('.', $value[0]);
                    }
                    $SQL .= $value[3].' '.$value[0].$value[1].'? ';
                    $values[] = $value[2];
                }
            }

            if (count($this->where_in)){
                foreach($this->where_in as $value){
                    $SQL    .= $value[2].' '.$value[0].' IN (';
                    for ($i=0; $i<count($value[1]); $i++){
                        $SQL .= '?, ';
                        $values[] = $value[1][$i];
                    }
                    $SQL = substr($SQL, 0, -2).') ';
                }
            }

            if (count($this->limit)){
                $SQL .= 'LIMIT '.$this->limit[0].', '.$this->limit[1];
            }

            $this->lastSQL = $SQL;
            $this->lastSQLParams = $values;
            return Db::unbufferedQuery($SQL, $values);
        } else {
            return FALSE;
        }
    }

    /**
     * delete record from table
     *
     * @return bool
     */
    public function delete(){
        if ($this->table != ''){
            $SQL    = 'DELETE FROM '.$this->table;
            $values = FALSE;
            if (count($this->where)){
                foreach($this->where as $value){
                    if (is_array($value[0])){
                        $value[0] = implode('.', $value[0]);
                    }
                    $SQL .= $value[3].' '.$value[0].$value[1].'? ';
                    $values[] = $value[2];
                }
            }

            if (count($this->where_in)){
                foreach($this->where_in as $value){
                    $SQL    .= $value[2].' '.$value[0].' IN (';
                    for ($i=0; $i<count($value[1]); $i++){
                        $SQL .= '?, ';
                        $values[] = $value[1][$i];
                    }
                    $SQL = substr($SQL, 0, -2).') ';
                }
            }

            if (count($this->limit)){
                $SQL .= 'LIMIT '.$this->limit[0].', '.$this->limit[1];
            }

            $this->lastSQL = $SQL;
            $this->lastSQLParams = $values;
            return Db::unbufferedQuery($SQL, $values);
        } else {
            return FALSE;
        }
    }

    /**
     * this function is for getting total row count without limit after executed SQL
     *
     * @return integer
     */
    public function getTotalCount(){
        $result = Db::Query('SELECT FOUND_ROWS()');
        return $result[0]['FOUND_ROWS()'];
    }

    /**
     * magic __call method to capture where_dynamic methods
     *
     * @param string $method
     * @param array $params
     * @return $this
     * @throws \Exception
     */
    public function __call($method, $params){
        if (strpos($method, 'where_') === 0){
            return $this->where_dynamic($method, $params);
        }

        throw new \Exception('This class ('.get_class($this).') is not supporting this method ('.$method.') yet!');
    }

}































